﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace SqlImEx.Sql
{
    public class SqlContext : ISqlContext
    {
        SqlConnection _conn;
        public bool KeepAlive { get; set; }
        public SqlContext(string connectionString)
        {
            _conn = new SqlConnection(connectionString);
        }

        public void ExecuteSql(string query)
        {
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
                command.ExecuteNonQuery();
            if (!KeepAlive)
                _conn.Close();
        }
        public DataTable ExecuteSqlQuery(string query)
        {
            DataTable dt;
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
            {
                using (IDataReader rdr = command.ExecuteReader())
                {
                    dt = new DataTable();
                    dt.Load(rdr);
                }
            }
            if (!KeepAlive)
                _conn.Close();
            return dt;
        }

        public TableDefinition GetTableData(string schema, string table)
        {
            var td = new TableDefinition();
            string query = @"SELECT s.name'schema',o.name'table'
FROM SYS.OBJECTS o
inner join sys.schemas s on s.schema_id = o.schema_id
WHERE s.name = @schema and o.name = @table";

            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
            {
                command.Parameters.AddWithValue("@schema", schema);
                command.Parameters.AddWithValue("@table", table);
                using (var rdr = command.ExecuteReader(CommandBehavior.SingleResult))
                {
                    rdr.Read();
                    td.name = (string)rdr["table"];
                    td.schema = (string)rdr["schema"];

                }
            }
            GetFieldData(td);
            if (!KeepAlive)
                _conn.Close();
            return td;
        }

        public List<TableDefinition> GetTableData()
        {
            var result = new List<TableDefinition>();
            string query = @"SELECT s.name'schema',o.name'table'
FROM SYS.OBJECTS o
inner join sys.schemas s on s.schema_id = o.schema_id
WHERE type in ('U') and is_ms_shipped = 0 
order by s.name,o.name";

            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
            {
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var td = new TableDefinition();
                        td.name = (string)rdr["table"];
                        td.schema = (string)rdr["schema"];
                        result.Add(td);
                    }
                }
            }
            if (!KeepAlive)
                _conn.Close();
            return result;
        }

        public void SetIdentity(string schema, string table, bool on)
        {
            string formattedTable = "[" + table + "]";
            if (!string.IsNullOrEmpty(schema))
            {
                formattedTable = "[" + schema + "]." + formattedTable;
            }
            string query = string.Format("SET IDENTITY_INSERT {0} {1}", formattedTable, on ? "ON" : "OFF");
            ExecuteSql(query);
        }

        public void DeleteAllFrom(TableDefinition tbl)
        {
            string delstr = "Delete from " + tbl.FullyQualifiedSqlName;
            ExecuteSql(delstr);
        }

        public void SetConstraints(bool enable, string schema, string table)
        {
            ExecuteSql(string.Format("ALTER TABLE [{0}].[{1}] {2}  CONSTRAINT all", schema, table, enable ? "CHECK" : "NOCHECK"));
        }

        //public void SetConstraints(bool enable)
        //{
        //    List<TableSchema> dtTables = this.FetchTables();
        //    bool keepalive = KeepAlive;
        //    KeepAlive = true;
        //    foreach (TableSchema table in dtTables)
        //    {
        //        ExecuteSql(string.Format("ALTER TABLE [{0}].[{1}] {2}  CONSTRAINT all", table.Schema, table.Table, enable ? "CHECK" : "NOCHECK"));
        //    }
        //    KeepAlive = keepalive;
        //}

        public List<TableSchema> FetchTables()
        {
            DataTable dt = ExecuteSqlQuery(@"Select s.name'Schema',t.name'Name'
from sys.tables t 
inner join sys.schemas s on t.schema_id = s.schema_id 
and is_ms_shipped = 0
order by s.name, t.[name]");
            List<TableSchema> tss = new List<TableSchema>();
            foreach (DataRow r in dt.Rows)
            {
                tss.Add(new TableSchema
                {
                    Schema = (string)r["Schema"],
                    Table = (string)r["Name"]
                });
            }
            return tss;
        }

        public List<IdentityColumn> GetIdentityColumns()
        {
            List<IdentityColumn> result = new List<IdentityColumn>();
            string query = "select s.name'Schema',t.name'Name', id.name 'ColumnName', id.seed_value, id.increment_value, id.last_value, id.is_not_for_replication\r\nfrom sys.identity_columns id\r\ninner join sys.tables t on t.object_id = id.object_id\r\ninner join sys.schemas s on t.schema_id = s.schema_id\r\norder by t.name";
            DataTable dt = ExecuteSqlQuery(query);
            foreach (DataRow r in dt.Rows)
            {
                result.Add(new IdentityColumn
                {
                    Table = (string)r["Name"],
                    Schema = (string)r["Schema"],
                    Column = (string)r["ColumnName"],
                    SeedValue = Convert.ToInt64(r["seed_value"]),
                    IncrementValue = Convert.ToInt64(r["increment_value"]),
                    NotForReplication = Convert.ToBoolean(r["is_not_for_replication"])
                });
            }
            return result;
        }

        public void GetFieldData(TableDefinition t)
        {
            var result = new List<Field>();
            string query = string.Format(@"select s.name'schema',t.name'table', c.name'column',ty.name'type',c.is_nullable,is_identity, is_computed,c.max_length
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.types ty on c.system_type_id = ty.system_type_id and c.system_type_id = ty.user_type_id
--inner join sys.types tys on ty.user_type_id = tys.user_type_id
where s.name = @schema and t.name = @table 
order by s.name,t.name", t.name, t.schema);
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
            {
                command.Parameters.AddWithValue("@table", t.name);
                command.Parameters.AddWithValue("@schema", t.schema);
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var fd = new Field();
                        fd.name = (string)rdr["column"];
                        fd.nulls = (bool)rdr["is_nullable"];
                        fd.datatype = DataTypeConversion.GetType((string)rdr["type"]);
                        fd.len = rdr["max_length"] != DBNull.Value ? Convert.ToInt32(rdr["max_length"]) : -1;
                        fd.identity = Convert.ToBoolean(rdr["is_identity"]);
                        result.Add(fd);
                    }
                }
            }
            //foreach (var k in _keys.Where(x => x.TableSchema == t.schema && x.TableName == t.name))
            //    result.First(x => x.name == k.ColumnName).key = true;
            if (!KeepAlive)
                _conn.Close();
            t.Field = result.ToArray();
        }

        public void GetAllRows(TableDefinition t, Action<IDataReader> row)
        {
            string query = "select * from " + t.FullyQualifiedSqlName;
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = new SqlCommand(query, _conn))
            {
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                        row(rdr);
                }
            }
            if (!KeepAlive)
                _conn.Close();
        }

        public void SetConstraints(bool enable)
        {
            foreach (var td in GetTableData())
            {
                SetConstraints(enable, td.schema, td.name);
            }
        }
    }
}
